package Bean;
import java.sql.*;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.*;
public class JdbcUtil {
    private Connection con = null;
    private Statement stmt = null;
    private String url = "jdbc:mysql://localhost:3306/ib_data_sources?characterEncoding=GBK&serverTimezone=UTC";
    private String user = "root";
    private String pwd = "root";

    public JdbcUtil() {
        init();
    }
    private void init(){
        try{
            Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
            con = DriverManager.getConnection(url ,user ,pwd );
            stmt = con.createStatement();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public ArrayList searchName(String name) throws SQLException {
        String sql = "SELECT * FROM test01 WHERE contents LIKE '%" + name + "%' UNION ALL SELECT * FROM test02 WHERE contents LIKE '%" + name + "%' UNION ALL SELECT * FROM test03 WHERE contents LIKE '%" + name + "%' UNION ALL SELECT * FROM test04 WHERE contents LIKE '%" + name + "%';";
//        ArrayList<String> resultLis = null;
//        ArrayList<String> names = null;
//        ArrayList<String> inst = null;
//        ArrayList<String> pages_num = null;
//        ArrayList<String> keywords = null;
        ArrayList resultsarray = new ArrayList();
        try {
            ResultSet rs = stmt.executeQuery(sql);
            ArrayList<String> authors = new ArrayList<String>();
            ArrayList<String> addresses = new ArrayList<String>();
            ArrayList<Integer> pagesnum = new ArrayList<Integer>();
            ArrayList<ArrayList<String>> keywords = new ArrayList<ArrayList<String>>();
            ArrayList<ArrayList<String>> articles = new ArrayList<ArrayList<String>>();
            ArrayList<ArrayList<String>> collaborator = new ArrayList<ArrayList<String>>();
            while (rs.next()) {
                String contents = rs.getString("contents");
                String author = rs.getString("author");
//                System.out.println("contents:" + contents);
                List<String> lis_contents = stringTolist(contents);
                ArrayList<String> keyword_lis = new ArrayList<String>();
                ArrayList<String> titles = new ArrayList<String>();
                for (int i = 0; i < lis_contents.size() ; i ++){
//                    System.out.println(lis_contents.get(i));

                    HashMap map_contents = stringToMap(lis_contents.get(i));
//                    System.out.println(map_contents.get("sno"));
//                    System.out.println(map_contents.get("lypm"));
                    String keyword = (String) map_contents.get("byc");
                    String article = (String) map_contents.get("lypm");
                    keyword = keyword.substring(3,keyword.length()-4);
                    List<String> kw_lis = Arrays.asList(keyword.split("aaa;aaa"));
                    for (String g:kw_lis){
                        keyword_lis.add(g);
                    }
                    titles.add(article);
//                    System.out.println(keyword_lis);
                }

//                System.out.println("author:" + author);
                List<String> lis_author = stringTolist(author);
                for (int i = 0; i < lis_author.size() ; i ++){
//                    System.out.println(lis_author.get(i));
                    //{'id': '6974966', 'sno': '11G0452015060005', 'zzpm': '1', 'fwxs': '0'}字符串，接下来要把这个字符串转为map
                    HashMap map_author = stringToMap(lis_author.get(i));
                    String namesingle = (String) map_author.get("zzmc");
                    String address = (String) map_author.get("txdz");
                    //txdz如果为空的话，改用jgmc+bmmc
                    if (address == null){
                        address = (String) map_author.get("jgmc") + (String) map_author.get("txdz");
                    }
//                    System.out.println(map_author.get("zzmc"));
//                    System.out.println(map_author.get("txdz"));
                    //先判断作者元素表中有哪些与我们需要查找的作者匹配
                    if (namesingle.contains(name)){
                        //如果匹配的话，先看是authors列表中是否已经存在
                        //如果名称和地址都匹配，那么就不需要再加入了
                        if (authors.contains(namesingle) && addresses.contains(address)){
                            //找到地址对应索引值
                            int index_add = addresses.indexOf(address);
                            //对应论文数量列表的元素加一
                            pagesnum.set(index_add,pagesnum.get(index_add)+1);
                            //研究主题，先判断现在的列表中是否有研究主题，有的话跳过，没有的话加入进去
                            for (String w:keyword_lis){
                                if (keywords.get(index_add).contains(w)){
                                    continue;
                                }else {
                                    keywords.get(index_add).add(w);
                                }
                            }

                            articles.get(index_add).add(titles.get(0));
                        } else if (authors.contains(namesingle)){
                            //如果只是人名匹配那么需要重新加入
                            authors.add(namesingle);
                            addresses.add(address);
                            pagesnum.add(1);
                            keywords.add(keyword_lis);
                            articles.add(titles);
                        }else {
                            //如果都不匹配的话，那么也要加入
                            authors.add(namesingle);
                            addresses.add(address);
                            pagesnum.add(1);
                            keywords.add(keyword_lis);
                            articles.add(titles);
                        }
                    }else {
                        continue;
                    }

                }
            }
//            System.out.println(authors);
//            System.out.println(addresses);
//            System.out.println(pagesnum);
//            System.out.println(keywords);
            resultsarray.add(authors);
            resultsarray.add(addresses);
            resultsarray.add(pagesnum);
            resultsarray.add(keywords);
            resultsarray.add(articles);
            for (int k = 0; k < authors.size();k++){
                //要删除的作者名
                String del_name = authors.get(k);
                for (String z:articles.get(k)){
                    ArrayList<String> friend = friends(del_name,z);
                    collaborator.add(friend);
                }
            }
            resultsarray.add(collaborator);
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultsarray;

    }
    public List<String> stringTolist(String str){
        List<String> result;
        String regex1 = "}, \\{";
        String regex2 = "}&%@{";
        //先去除首位的[]
        str = str.substring(1,str.length()-1);
        //用正则表达式匹配，将}，{转换为}&%@{
        str = str.replaceAll(regex1,regex2);
        //按照依据进行split匹配
        result = Arrays.asList(str.split("&%@"));
        return result;
    }
    public HashMap<String,String> stringToMap(String str){
        //{'id': '6974966', 'sno': '11G0452015060005', 'zzpm': '1', 'fwxs': '0'}字符串str，接下来要把这个字符串转为map
        HashMap<String,String> map = new HashMap<String, String>();
        List<String> list = stringTolist4params(str,"', '","'&%@'","&%@");
        //[''id': '6974966'', ''sno': '11G0452015060005'', ''zzpm': '1'', ''fwxs': '0'', ''zzmc': '付大军'']
//        System.out.println("拆分后的结构：");
//        System.out.println(list);
//        System.out.println(list.get(0));
        for (String i:list){
            //单个i为'id': '6974966'
            List<String> list_single = stringTolist4params(i,"': '","&%@","&%@");
            String a = list_single.get(0);
            try{
                String b = list_single.get(1);
                map.put(a,b);
            }catch (Exception e){
                String b = "";
                map.put(a,b);
            }
        }
        return map;
    }
    public List<String> stringTolist4params(String str,String regex1,String regex2,String sp){
        List<String> result;
        //先去除首位的[]
        str = str.substring(1,str.length()-1);
        //用正则表达式匹配，将}，{转换为}&%@{
        str = str.replaceAll(regex1,regex2);
        //按照依据进行split匹配
        result = Arrays.asList(str.split(sp));
        return result;
    }
    public ArrayList searchArea(String area) throws SQLException {
        String sql = "SELECT * FROM test01 WHERE contents LIKE '%" + area + "%' UNION ALL SELECT * FROM test02 WHERE contents LIKE '%" + area + "%' UNION ALL SELECT * FROM test03 WHERE contents LIKE '%" + area + "%' UNION ALL SELECT * FROM test04 WHERE contents LIKE '%" + area + "%';";
//        ArrayList<String> resultLis = null;
//        ArrayList<String> names = null;
//        ArrayList<String> inst = null;
//        ArrayList<String> pages_num = null;
//        ArrayList<String> keywords = null;
        ArrayList resultsarray = new ArrayList();
        try {
            ResultSet rs = stmt.executeQuery(sql);
            ArrayList<String> authors = new ArrayList<String>();
            ArrayList<String> addresses = new ArrayList<String>();
            ArrayList<Integer> pagesnum = new ArrayList<Integer>();
            ArrayList<ArrayList<String>> keywords = new ArrayList<ArrayList<String>>();
            ArrayList<ArrayList<String>> articles = new ArrayList<ArrayList<String>>();
            ArrayList<ArrayList<String>> collaborator = new ArrayList<ArrayList<String>>();
            while (rs.next()) {
                String contents = rs.getString("contents");
                String author = rs.getString("author");
//                System.out.println("contents:" + contents);
                List<String> lis_contents = stringTolist(contents);
                ArrayList<String> keyword_lis = new ArrayList<String>();
                ArrayList<String> titles = new ArrayList<String>();
                for (int i = 0; i < lis_contents.size() ; i ++){
//                    System.out.println(lis_contents.get(i));
                    HashMap map_contents = stringToMap(lis_contents.get(i));
//                    System.out.println(map_contents.get("sno"));
//                    System.out.println(map_contents.get("lypm"));
                    String keyword = (String) map_contents.get("byc");
                    String article = (String) map_contents.get("lypm");
                    keyword = keyword.substring(3,keyword.length()-4);
                    List<String> kw_lis = Arrays.asList(keyword.split("aaa;aaa"));
                    for (String g:kw_lis){
                        keyword_lis.add(g);
                    }
                    titles.add(article);
//                    System.out.println(keyword_lis);
                }

//                System.out.println("author:" + author);
                List<String> lis_author = stringTolist(author);

                for (int i = 0; i < lis_author.size() ; i ++){
//                    System.out.println(lis_author.get(i));
                    //{'id': '6974966', 'sno': '11G0452015060005', 'zzpm': '1', 'fwxs': '0'}字符串，接下来要把这个字符串转为map
                    HashMap map_author = stringToMap(lis_author.get(i));
                    String namesingle = (String) map_author.get("zzmc");
                    String address = (String) map_author.get("txdz");
                    //txdz如果为空的话，改用jgmc+bmmc
                    if (address == null){
                        address = (String) map_author.get("jgmc") + (String) map_author.get("txdz");
                    }
//                    System.out.println(map_author.get("zzmc"));
//                    System.out.println(map_author.get("txdz"));
                    //先判断作者元素表中有哪些与我们需要查找的作者匹配
//                    if (namesingle.contains(name)){
//                        //如果匹配的话，先看是authors列表中是否已经存在
//                        //如果名称和地址都匹配，那么就不需要再加入了
                    if (authors.contains(namesingle) && addresses.contains(address)){
                        //找到地址对应索引值
                        int index_add = addresses.indexOf(address);
                        //对应论文数量列表的元素加一
                        pagesnum.set(index_add,pagesnum.get(index_add)+1);
                        //研究主题，先判断现在的列表中是否有研究主题，有的话跳过，没有的话加入进去
                        for (String w:keyword_lis){
                            if (keywords.get(index_add).contains(w)){
                                continue;
                            }else {
                                keywords.get(index_add).add(w);
                            }
                        }
                        articles.get(index_add).add(titles.get(0));
                    } else if (authors.contains(namesingle)){
                        //如果只是人名匹配那么需要重新加入
                        authors.add(namesingle);
                        addresses.add(address);
                        pagesnum.add(1);
                        keywords.add(keyword_lis);
                        articles.add(titles);
                    }else {
                        //如果都不匹配的话，那么也要加入
                        authors.add(namesingle);
                        addresses.add(address);
                        pagesnum.add(1);
                        keywords.add(keyword_lis);
                        articles.add(titles);
                    }
//                    }else {
//                        continue;
//                    }

                }
            }
//            System.out.println(authors);
//            System.out.println(addresses);
//            System.out.println(pagesnum);
//            System.out.println(keywords);
            resultsarray.add(authors);
            resultsarray.add(addresses);
            resultsarray.add(pagesnum);
            resultsarray.add(keywords);
            resultsarray.add(articles);
            for (int k = 0; k < authors.size();k++){
                //要删除的作者名
                String del_name = authors.get(k);
                for (String z:articles.get(k)){
                    ArrayList<String> friend = friends(del_name,z);
                    collaborator.add(friend);
                }
            }
            resultsarray.add(collaborator);
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultsarray;
    }
    public void close() throws SQLException {
        stmt.close();
        con.close();
    }
    public ArrayList<String> friends(String author, String title) throws SQLException {
        ArrayList<String> result = new ArrayList<String >();
        String sql = "SELECT * FROM test01 WHERE contents LIKE '%" + title + "%' UNION ALL SELECT * FROM test02 WHERE contents LIKE '%" + title + "%' UNION ALL SELECT * FROM test03 WHERE contents LIKE '%" + title + "%' UNION ALL SELECT * FROM test04 WHERE contents LIKE '%" + title + "%';";
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()){
            String authors = rs.getString("author");
            List<String> lis_author = stringTolist(authors);
            for (int i = 0; i < lis_author.size() ; i ++){
//                    System.out.println(lis_author.get(i));
                //{'id': '6974966', 'sno': '11G0452015060005', 'zzpm': '1', 'fwxs': '0'}字符串，接下来要把这个字符串转为map
                HashMap map_author = stringToMap(lis_author.get(i));
                String namesingle = (String) map_author.get("zzmc");
                result.add(namesingle);
            }
        }
        result.remove(author);
        return result;
    }
}